package cn.powerone.scm.service.impl;

import java.io.InputStream;
import java.util.*;

import cn.powerone.scm.domain.*;
import cn.powerone.scm.mapper.*;
import cn.powerone.scm.service.IScmCustomerOrderItemsService;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import cn.powerone.scm.service.IScmInventoryLogRequestService;
import org.springframework.web.multipart.MultipartFile;

/**
 * 出入库请求记录Service业务层处理
 * 
 * @author PowerOne
 * @date 2025-07-27
 */
@Service
public class ScmInventoryLogRequestServiceImpl implements IScmInventoryLogRequestService 
{
    @Autowired
    private ScmInventoryLogRequestMapper scmInventoryLogRequestMapper;

    @Autowired
    private ScmSupplierOrderItemsMapper scmSupplierOrderItemsMapper;

    @Autowired
    private ScmSupplierOrdersMapper scmSupplierOrdersMapper;
    @Autowired
    private ScmCustomerOrdersMapper scmCustomerOrdersMapper;

    @Autowired
    private ScmProjectProductsServiceImpl  scmProjectProductsService;

    @Autowired
    private IScmCustomerOrderItemsService scmCustomerOrderItemsService;

    @Autowired
    private ScmInventoryProductLocationMapper scmInventoryProductLocationMapper;


    @Autowired
    private ScmInventoryWarehouseLocationMapper scmInventoryWarehouseLocationMapper;
    /**
     * 查询出入库请求记录
     * 
     * @param id 出入库请求记录主键
     * @return 出入库请求记录
     */
    @Override
    public ScmInventoryLogRequest selectScmInventoryLogRequestById(Long id)
    {
        return scmInventoryLogRequestMapper.selectScmInventoryLogRequestById(id);
    }

    /**
     * 查询出入库请求记录列表
     * 
     * @param scmInventoryLogRequest 出入库请求记录
     * @return 出入库请求记录
     */
    @Override
    public List<ScmInventoryLogRequest> selectScmInventoryLogRequestList(ScmInventoryLogRequest scmInventoryLogRequest)
    {
        return scmInventoryLogRequestMapper.selectScmInventoryLogRequestList(scmInventoryLogRequest);
    }

    /**
     * 新增出入库请求记录
     * 
     * @param scmInventoryLogRequest 出入库请求记录
     * @return 结果
     */
    @Override
    public int insertScmInventoryLogRequest(ScmInventoryLogRequest scmInventoryLogRequest)
    {
        return scmInventoryLogRequestMapper.insertScmInventoryLogRequest(scmInventoryLogRequest);
    }

    /**
     * 修改出入库请求记录
     * 
     * @param scmInventoryLogRequest 出入库请求记录
     * @return 结果
     */
    @Override
    public int updateScmInventoryLogRequest(ScmInventoryLogRequest scmInventoryLogRequest)
    {
        return scmInventoryLogRequestMapper.updateScmInventoryLogRequest(scmInventoryLogRequest);
    }

    /**
     * 批量删除出入库请求记录
     * 
     * @param ids 需要删除的出入库请求记录主键
     * @return 结果
     */
    @Override
    public int deleteScmInventoryLogRequestByIds(Long[] ids)
    {
        return scmInventoryLogRequestMapper.deleteScmInventoryLogRequestByIds(ids);
    }

    /**
     * 删除出入库请求记录信息
     * 
     * @param id 出入库请求记录主键
     * @return 结果
     */
    @Override
    public int deleteScmInventoryLogRequestById(Long id)
    {
        return scmInventoryLogRequestMapper.deleteScmInventoryLogRequestById(id);
    }


    @Override
    public List<Map<String, Object>> processXlsAndGenerateRequestInLibPlans(MultipartFile file) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();

        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0);

            // 表头只会在第一行
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                throw new Exception("未找到表头行");
            }

            // 查找表头列索引
            int serialColIndex = -1;
            int drawingColIndex = -1;
            int quantityColIndex = -1;
            for (int j = 0; j < headerRow.getLastCellNum(); j++) {
                String cellValue = getCellValueAsString(headerRow.getCell(j));
                if ("序列".equals(cellValue)) {
                    serialColIndex = j;
                } else if ("图号".equals(cellValue)) {
                    drawingColIndex = j;
                } else if ("数量".equals(cellValue)) {
                    quantityColIndex = j;
                }
            }

            // 如果没有找到表头，抛出异常
            if (serialColIndex == -1 || drawingColIndex == -1 || quantityColIndex == -1) {
                throw new Exception("未找到包含'序列'、'图号'、'数量'的表头行");
            }

            // 处理数据行，从第二行开始（索引为1）
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) break; // 遇到空行结束

                // 检查是否是空行
                boolean isEmptyRow = true;
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    if (getCellValueAsString(row.getCell(j)) != null && !getCellValueAsString(row.getCell(j)).trim().isEmpty()) {
                        isEmptyRow = false;
                        break;
                    }
                }
                if (isEmptyRow) break; // 遇到空行结束

                String serial = getCellValueAsString(row.getCell(serialColIndex)); // 序列
                String drawing = getCellValueAsString(row.getCell(drawingColIndex)); // 图号
                String quantityStr = getCellValueAsString(row.getCell(quantityColIndex)); // 数量

                // 处理数据
                if (drawing != null && !drawing.trim().isEmpty() && quantityStr != null && !quantityStr.trim().isEmpty() && serial != null && !serial.trim().isEmpty()) {
                    int quantity;
                    try {
                        quantity = (int) Double.parseDouble(quantityStr);
                    } catch (NumberFormatException e) {
                        continue; // 如果数量不是有效数字，跳过该行
                    }

                    // 为每个序列号创建一个条目
                    Map<String, Object> serialEntry = new HashMap<>();
                    serialEntry.put("序列", serial);
                    serialEntry.put("图号", drawing);
                    serialEntry.put("数量", quantity);
                    List<Map<String, Object>> plans = new ArrayList<>();
                    serialEntry.put("plan", plans);

                    // 1. 用图号做条件在scm_project_product搜索
                    ScmProjectProducts projectProductQuery = new ScmProjectProducts();
                    projectProductQuery.setDrawingNumber(drawing);
                    List<ScmProjectProducts> projectProducts = scmProjectProductsService.selectScmProjectProductsListByScmProjectProducts(projectProductQuery);

                    if (projectProducts == null || projectProducts.isEmpty()) {
                        // 没有找到匹配的项目产品，标记状态
                        serialEntry.put("status", "error");
                        serialEntry.put("reason", "未找到匹配的项目产品");
                        result.add(serialEntry);
                        continue;
                    }

                    // 处理所有匹配的项目产品
                    int remainingQuantity = quantity;
                    Long locationId = 0L;
                    Long warehouseId = 0L;

                    for (ScmProjectProducts projectProduct : projectProducts) {
                        if (remainingQuantity <= 0) break;

                        ScmSupplierOrders orderQuery = new ScmSupplierOrders();
                        orderQuery.setProjectId(projectProduct.getProjectId());
                        List<ScmSupplierOrders> orderItems = scmSupplierOrdersMapper.selectScmSupplierOrdersList(orderQuery);

                        for(ScmSupplierOrders orderItem : orderItems){
                            // 1 根据proudct_id ,查找location_id, warehouse_id


                            ScmInventoryProductLocation prodLoc = new ScmInventoryProductLocation();
                            prodLoc.setProductId(projectProduct.getProductId());
                            List<ScmInventoryProductLocation> prodLocation= scmInventoryProductLocationMapper.selectScmInventoryProductLocationList(prodLoc);

                            if(prodLocation.size()>=1){
                                locationId = prodLocation.get(0).getLocationId();
                            }
                            ScmInventoryWarehouseLocation warehouse = new ScmInventoryWarehouseLocation();
                            Long locationId1 = prodLocation.get(0).getLocationId();
                            ScmInventoryWarehouseLocation warehouseLocation= scmInventoryWarehouseLocationMapper.selectScmInventoryWarehouseLocationById(locationId1);
                            warehouseId = warehouseLocation.getWarehouseId();





                            // 2. 根据project_id, id , orderId在scm_supplier_order_items里搜索
                            ScmSupplierOrderItemsWithOrderInfoVO itemQuery = new ScmSupplierOrderItemsWithOrderInfoVO();
                            itemQuery.setProjectProductId(projectProduct.getId());
                            itemQuery.setProductId(projectProduct.getProductId());
                            itemQuery.setOrderId(orderItem.getId());

                            List<ScmSupplierOrderItemsWithOrderInfoVO> matchingItems = scmSupplierOrderItemsMapper.selectScmSupplierOrderItemsListWithOrderInfo(itemQuery);

                            // 按item_id升序排列
                            Collections.sort(matchingItems, (a, b) -> a.getItemId().compareTo(b.getItemId()));


                            // 3. 对于每一条数据，检查quantityInLib 和 Quantity
                            for (ScmSupplierOrderItemsWithOrderInfoVO item : matchingItems) {
                                if (remainingQuantity <= 0) break;

                                long quantityInLib = item.getQuantityInLib();
                                long totalQuantity = item.getQuantity().longValue();

                                // 如果已经入库完毕，忽略这条记录
                                if (quantityInLib >= totalQuantity) {
                                    continue;
                                }

                                // 可以入库的数量
                                long availableQuantity = totalQuantity - quantityInLib;

                                // 如果需要入库的数量小于等于可入库数量
                                long allocatedQuantity;
                                if (remainingQuantity <= availableQuantity) {
                                    // 全部入到这个订单
                                    allocatedQuantity = remainingQuantity;
                                    remainingQuantity = 0;
                                } else {
                                    // 部分入到这个订单
                                    allocatedQuantity = availableQuantity;
                                    remainingQuantity -= availableQuantity;
                                }

                                // 创建plan条目，包含ScmSupplierOrderItemsWithOrderInfoVO里的所有元素
                                Map<String, Object> plan = new HashMap<>();
                                // 添加ScmSupplierOrderItemsWithOrderInfoVO的所有属性
                                plan.put("itemId", item.getItemId());
                                plan.put("orderId", item.getOrderId());
                                plan.put("productId", item.getProductId());
                                plan.put("projectProductId", item.getProjectProductId());
                                plan.put("customerQuoteId", item.getCustomerQuoteId());
                                plan.put("supplierId", item.getSupplierId());
                                plan.put("supplierQuoteId", item.getSupplierQuoteId());
                                plan.put("supplierName", item.getSupplierName());
                                plan.put("purchaseCategory", item.getPurchaseCategory());
                                plan.put("demandType", item.getDemandType());
                                plan.put("manufactureNumber", item.getManufactureNumber());
                                plan.put("manufactureSubNumber", item.getManufactureSubNumber());
                                plan.put("drawingNumberModel", item.getDrawingNumberModel());
                                plan.put("size", item.getSize());

                                plan.put("unitPriceType", item.getUnitPriceType());
                                plan.put("unitPrice", item.getUnitPrice());
                                plan.put("quantity", item.getQuantity());
                                plan.put("amount", item.getAmount());
                                plan.put("moldCost", item.getMoldCost());
                                plan.put("jigCost", item.getJigCost());
                                plan.put("supplierCurrency", item.getSupplierCurrency());
                                plan.put("supplierCurrencyRate", item.getSupplierCurrencyRate());
                                plan.put("requestedDeliveryDate", item.getRequestedDeliveryDate());
                                plan.put("repliedDeliveryDate", item.getRepliedDeliveryDate());
                                plan.put("remarks", item.getRemarks());
                                plan.put("mainOrderCode", item.getMainOrderCode());
                                plan.put("quantityInLib", item.getQuantityInLib());
                                plan.put("availableInventory", item.getAvailableInventory());

                                Map<String, Object> projectProductItem = new HashMap<>();
                                projectProductItem.put("model", projectProduct.getModel());
                                projectProductItem.put("drawingNumber", projectProduct.getDrawingNumber());
                                projectProductItem.put("drawingVersion", projectProduct.getVersion());
                                projectProductItem.put("productName", projectProduct.getProductName());
                                projectProductItem.put("material", projectProduct.getMaterial());
                                projectProductItem.put("specification", projectProduct.getSpecification());
                                projectProductItem.put("color", projectProduct.getColor());

                                plan.put("scmProjectProducts", projectProductItem);

                                // 添加额外的入库相关字段
                                plan.put("currentRequestInQuantity", allocatedQuantity);
                                plan.put("warehouseId", warehouseId); // 需要根据业务逻辑设置
                                plan.put("locationId", locationId); // 需要根据业务逻辑设置

                                plans.add(plan);

                                // 如果数量已经全部分配完，跳出循环
                                if (remainingQuantity <= 0) break;
                            }

                        }

                    }

                    // 设置状态
                    if (remainingQuantity == 0) {
                        serialEntry.put("status", "success");
                        serialEntry.put("unprocessedQuantity", remainingQuantity);
                        serialEntry.put("reason", "分配成功");
                    } else if (plans.isEmpty()) {
                        serialEntry.put("status", "error");
                        serialEntry.put("unprocessedQuantity", remainingQuantity);
                        serialEntry.put("reason", "分配失败没有找到匹配的订单项");
                    } else {
                        serialEntry.put("status", "partial");
                        serialEntry.put("unprocessedQuantity", remainingQuantity);
                        serialEntry.put("reason", "没有足够的订单来接收全部数量");
                    }

                    result.add(serialEntry);
                }
            }
        }
        return result;
    }


    @Override
    public List<Map<String, Object>> processXlsAndGenerateRequestOutLibPlans(MultipartFile file) throws Exception {
        List<Map<String, Object>> result = new ArrayList<>();
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0);

            // 表头只会在第一行
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                throw new Exception("未找到表头行");
            }

            // 查找表头列索引
            int serialColIndex = -1;
            int drawingColIndex = -1;
            int quantityColIndex = -1;
            for (int j = 0; j < headerRow.getLastCellNum(); j++) {
                String cellValue = getCellValueAsString(headerRow.getCell(j));
                if ("序列".equals(cellValue)) {
                    serialColIndex = j;
                } else if ("图号".equals(cellValue)) {
                    drawingColIndex = j;
                } else if ("数量".equals(cellValue)) {
                    quantityColIndex = j;
                }
            }

            // 如果没有找到表头，抛出异常
            if (serialColIndex == -1 || drawingColIndex == -1 || quantityColIndex == -1) {
                throw new Exception("未找到包含'序列'、'图号'、'数量'的表头行");
            }

            // 处理数据行，从第二行开始（索引为1）
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) break; // 遇到空行结束

                // 检查是否是空行
                boolean isEmptyRow = true;
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    if (getCellValueAsString(row.getCell(j)) != null && !getCellValueAsString(row.getCell(j)).trim().isEmpty()) {
                        isEmptyRow = false;
                        break;
                    }
                }
                if (isEmptyRow) break; // 遇到空行结束
                String serial = getCellValueAsString(row.getCell(serialColIndex)); // 序列
                String drawing = getCellValueAsString(row.getCell(drawingColIndex)); // 图号
                String needOutQuantity1 = getCellValueAsString(row.getCell(quantityColIndex)); // 数量

                // 处理数据
                if (drawing != null && !drawing.trim().isEmpty() && needOutQuantity1 != null && !needOutQuantity1.trim().isEmpty() && serial != null && !serial.trim().isEmpty()) {
                    int needOutQuantity;
                    try {
                        needOutQuantity = (int) Double.parseDouble(needOutQuantity1);
                    } catch (NumberFormatException e) {
                        continue; // 如果数量不是有效数字，跳过该行
                    }

                    // 为每个序列号创建一个条目
                    Map<String, Object> serialEntry = new HashMap<>();
                    serialEntry.put("序列", serial);
                    serialEntry.put("图号", drawing);
                    serialEntry.put("数量", needOutQuantity);
                    List<Map<String, Object>> plans = new ArrayList<>();
                    serialEntry.put("plan", plans);

                    // 1. 用图号做条件在scm_project_product搜索
                    ScmProjectProducts projectProductQuery = new ScmProjectProducts();
                    projectProductQuery.setDrawingNumber(drawing);
                    List<ScmProjectProducts> projectProducts = scmProjectProductsService.selectScmProjectProductsListByScmProjectProducts(projectProductQuery);

                    if (projectProducts == null || projectProducts.isEmpty()) {
                        // 没有找到匹配的项目产品，标记状态
                        serialEntry.put("status", "error");
                        serialEntry.put("reason", "未找到匹配的项目产品");
                        result.add(serialEntry);
                        continue;
                    }

                    // 处理所有匹配的项目产品

//                    int remainingQuantityForRequest = needOutQuantity;
                    Long locationId = 0L;
                    Long warehouseId = 0L;

                    for (ScmProjectProducts projectProduct : projectProducts) {
                        if (needOutQuantity <= 0) break;

                        ScmCustomerOrders orderQuery = new ScmCustomerOrders();
                        orderQuery.setProjectId(projectProduct.getProjectId());
                        List<ScmCustomerOrders> orderItems = scmCustomerOrdersMapper.selectScmCustomerOrdersList(orderQuery);

                        for(ScmCustomerOrders orderItem : orderItems){



                            ScmInventoryProductLocation prodLoc = new ScmInventoryProductLocation();
                            prodLoc.setProductId(projectProduct.getProductId());
                            List<ScmInventoryProductLocation> prodLocation= scmInventoryProductLocationMapper.selectScmInventoryProductLocationList(prodLoc);

                            if(prodLocation.size()>=1){
                                locationId = prodLocation.get(0).getLocationId();
                            }
                            ScmInventoryWarehouseLocation warehouse = new ScmInventoryWarehouseLocation();
                            Long locationId1 = prodLocation.get(0).getLocationId();
                            ScmInventoryWarehouseLocation warehouseLocation= scmInventoryWarehouseLocationMapper.selectScmInventoryWarehouseLocationById(locationId1);
                            warehouseId = warehouseLocation.getWarehouseId();




                            // 2. 根据project_id, id , orderId在scm_supplier_order_items里搜索
                            ScmCustomerOrderItemsWithOrderInfoVO itemQuery = new ScmCustomerOrderItemsWithOrderInfoVO();
                            itemQuery.setProjectProductId(projectProduct.getId());
                            itemQuery.setProductId(projectProduct.getProductId());
                            itemQuery.setOrderId(orderItem.getId());
                            List<ScmCustomerOrderItemsWithOrderInfoVO> matchingItems = scmCustomerOrderItemsService.selectScmSupplierOrderItemsListWithOrderInfo(itemQuery);


                            // 按item_id升序排列
                            Collections.sort(matchingItems, (a, b) -> a.getItemId().compareTo(b.getItemId()));
                            int availableInventory = matchingItems.isEmpty() ? 0 :  matchingItems.get(0).getAvailableInventory();

                            for (ScmCustomerOrderItemsWithOrderInfoVO order : matchingItems) {
                                int orderQuantity = order.getQuantity() == null ? 0 : order.getQuantity().intValue();
                                int outLibQuantity = (int) order.getQuantityOutLib();
                                int remainOutQuantityForOrder = orderQuantity - outLibQuantity;

                                if (remainOutQuantityForOrder <= 0 || needOutQuantity <= 0 || availableInventory <= 0) continue;

                                int allocateQuantity = Math.min(Math.min(remainOutQuantityForOrder, needOutQuantity), availableInventory);


                                Map<String, Object> plan = new HashMap<>();
                                plan.put("itemId", order.getItemId());
                                plan.put("orderId", order.getOrderId());
                                plan.put("productId", order.getProductId());
                                plan.put("projectProductId", order.getProjectProductId());
                                plan.put("drawingNumberModel", order.getDrawingNumberModel());
                                plan.put("quantity", order.getQuantity());
                                plan.put("quantityOutLib", order.getQuantityOutLib());
                                plan.put("availableInventory", availableInventory);

                                plan.put("mainOrderCode", order.getMainOrderCode());
                                plan.put("unitPriceType", order.getUnitPriceType());
                                plan.put("unitPrice", order.getUnitPrice());
                                plan.put("amount", order.getAmount());


                                Map<String, Object> projectProductItem = new HashMap<>();
                                projectProductItem.put("model", projectProduct.getModel());
                                projectProductItem.put("drawingNumber", projectProduct.getDrawingNumber());
                                projectProductItem.put("drawingVersion", projectProduct.getVersion());
                                projectProductItem.put("productName", projectProduct.getProductName());
                                projectProductItem.put("material", projectProduct.getMaterial());
                                projectProductItem.put("specification", projectProduct.getSpecification());
                                projectProductItem.put("color", projectProduct.getColor());
                                plan.put("scmProjectProducts", projectProductItem);


                                // 添加额外的入库相关字段
                                plan.put("currentRequestOutQuantity", allocateQuantity);
                                plan.put("warehouseId", warehouseId); // 需要根据业务逻辑设置
                                plan.put("locationId", locationId); // 需要根据业务逻辑设置



                                plans.add(plan);

                                needOutQuantity -= allocateQuantity;
                                availableInventory -= allocateQuantity;

                                if (needOutQuantity <= 0) break;
                            }

                        }

                    }

                    // 设置状态
                    if (needOutQuantity == 0) {
                        serialEntry.put("status", "success");
                        serialEntry.put("unprocessedQuantity", needOutQuantity);
                        serialEntry.put("reason", "分配成功");
                    } else if (plans.isEmpty()) {
                        serialEntry.put("status", "error");
                        serialEntry.put("unprocessedQuantity", needOutQuantity);
                        serialEntry.put("reason", "分配失败没有找到匹配的订单项");
                    } else {
                        serialEntry.put("status", "partial");
                        serialEntry.put("unprocessedQuantity", needOutQuantity);
                        serialEntry.put("reason", "没有足够的订单来接收全部数量");
                    }

                    result.add(serialEntry);
                }
            }
        }
        return result;
    }

    /**
     * 获取单元格的字符串值
     */
    private String getCellValueAsString(org.apache.poi.ss.usermodel.Cell cell) {
        if (cell == null) {
            return null;
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 数值类型转换为字符串，去掉.0
                    double numericValue = cell.getNumericCellValue();
                    if (numericValue == (long) numericValue) {
                        return String.valueOf((long) numericValue);
                    } else {
                        return String.valueOf(numericValue);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }
}
